﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ECOSYSTEM_Dominio;

namespace ECOSYSTEM_DAL
{
    public class EmpresaProvedorDAL
    {
        SqlConnection cnn;
        Int32 SiHay;
        public Boolean guardarEmpresaProvedor(EmpresaProvedor laEmpresaProvedor) { 
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("RegistrarEmpresaProvedor",cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@EmpresaProvedorID", SqlDbType.Int);
                cmd.Parameters["@EmpresaProvedorID"].Direction = ParameterDirection.InputOutput; ;
                cmd.Parameters.Add("@RUCEmpreProv", SqlDbType.NVarChar,11);
                cmd.Parameters.Add("@RazonSocial", SqlDbType.NVarChar,100);
                cmd.Parameters.Add("@FecReg", SqlDbType.NVarChar,12);
                
                cmd.Parameters["@EmpresaProvedorID"].Value = laEmpresaProvedor.EmpresaProvedorID;
                cmd.Parameters["@RUCEmpreProv"].Value = laEmpresaProvedor.EmpresaProvRUC;
                cmd.Parameters["@RazonSocial"].Value = laEmpresaProvedor.RazonSocialEmpreProv;
                cmd.Parameters["@FecReg"].Value = laEmpresaProvedor.FecRegEmpreProv;
                cmd.ExecuteNonQuery();
                laEmpresaProvedor.EmpresaProvedorID = (Int32)(cmd.Parameters["@EmpresaProvedorID"].Value);
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
       
        public Boolean ActualizarEmpresaProvedor(EmpresaProvedor laEmpresaProvedor, Int32 CodigoProv, String RazSocNuevo, String RucNuevo)
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ActualizarEmpresaProvedor", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@EmpresaProvedorID", SqlDbType.Int);                
                cmd.Parameters.Add("@Ruc", SqlDbType.NVarChar, 11);
                cmd.Parameters.Add("@RazSoc", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@RucNuevo", SqlDbType.NVarChar, 11);
                cmd.Parameters.Add("@RazSocNuevo", SqlDbType.NVarChar, 100);
                cmd.Parameters["@EmpresaProvedorID"].Value = CodigoProv;
                cmd.Parameters["@Ruc"].Value = laEmpresaProvedor.EmpresaProvRUC;
                cmd.Parameters["@RazSoc"].Value = laEmpresaProvedor.RazonSocialEmpreProv;
                cmd.Parameters["@RucNuevo"].Value = RucNuevo;
                cmd.Parameters["@RazSocNuevo"].Value = RazSocNuevo;
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }

        public DataTable ObtenerListaProvedor() {
            DataTable lista = null;
            try {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlDataAdapter da = new SqlDataAdapter("ObtenerListaProvedor", cnn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                lista = new DataTable();
                da.Fill(lista);
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return lista;
        }

        public Int32 NuevaEmpresaProvedorID(String nomEmpre, String RucEmpre, String FecReg) { 
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ObtenerUltimoCodigoEmpresaProvedorID", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@NomEmpresaProv", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@RucEmpresa", SqlDbType.NVarChar,11);
                cmd.Parameters.Add("@FecReg", SqlDbType.NVarChar, 12);
                cmd.Parameters["@NomEmpresaProv"].Value = nomEmpre;
                cmd.Parameters["@RucEmpresa"].Value = RucEmpre;
                cmd.Parameters["@FecReg"].Value = FecReg;
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SiHay = Convert.ToInt32(dr["NewEmpresaProvedorID"]);
                }
            }
            catch (SqlException e) { throw new Exception(e.Message); }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
            return SiHay;
        }
        public Int32 UltimoCodigoRegistradoEmpresaProvedorID()
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("UltimoCodigoRegistradoEmpresaProvedorID", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SiHay = Convert.ToInt32(dr["NewEmpresaProvedorID"]);
                }
            }
            catch (SqlException e) { throw new Exception(e.Message); }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
            return SiHay;
        }
    }
}
